Request: Map of Street Ends

1. Identify all nodeidfroms and nodeidtos of all segemnts in lion table

2. Join on exclusion (join nodeidfrom table and nodeidto table where they don't match)

3. After obtaining dead end node list, join back to node to obtain node geometry for mapping

In [3]:
from ris import db2  #library designed for SQL database connection and querying
from IPython.display import clear_output
import datetime 
from datetime import date
import pandas as pd
import os
import decimal
from sqlalchemy import create_engine
from ris import pg_import_export_shps as shp


clear_output()
timestamp = datetime.datetime.now().strftime('%Y-%m-%d %H:%M')
print 'Notebook run: {}'.format(timestamp)
print os.getcwd()
# %load_ext sql 
Notebook run: 2019-09-05 16:01
E:\RIS\Staff Folders\Samuel\Requests\AG\Dead_Ends
In [5]:
#Database connections
cdb = db2.PostgresDb('DOTDEVRHPGSQL01', 'CRASHDATA', quiet = True)
User name (CRASHDATA):soge
Password (CRASHDATA)········

Query containing all street end nodes and geometries

In [3]:
dead_ends =  cdb.query("""select tot.nodeid, n2.geom from (
                        select coalesce(nodeidto, nodeidfrom) nodeid from ( 
                            select distinct l.nodeidfrom --table containing all distinct nodeidfrom nodes
                            from lion l
                            join node n on --joins lion to node table (necessary to exclude virtual intersections)
                            n.nodeid = l.nodeidfrom::int 
                            where l.rb_layer in ('R', 'B', 'N') and l.featuretyp not in ('1', '2', '3', '7', 'A', 'F') --Removes all other elements that are not real streets
                            and n.vintersect is null -- Removes all virtual intersections
                            ) f
                            full join -- Joins both nodeidfrom table to nodeidto table on nodeidfrom=nodeidto
                            (
                            select distinct l.nodeidto --table containing all distinct nodeidto nodes
                            from lion l
                            join node n on --joins lion to node table (necessary to exclude virtual intersections)
                            n.nodeid = l.nodeidto::int
                            where l.rb_layer in ('R', 'B', 'N') and l.featuretyp not in ('1', '2', '3', '7', 'A', 'F') --Removes all other elements that are not real streets
                            and n.vintersect is null -- Removes all virtual intersections
                            ) t 
                        on f.nodeidfrom=t.nodeidto
                        where f.nodeidfrom is null or t.nodeidto is null) tot -- only considers nodes where nodeidfrom and nodeid don't match.
                    join node n2 -- joins dead_end table back to node table to retrieve geometries.
                    on tot.nodeid::int=n2.nodeid
                    """)
In [ ]:
dead_ends =  cdb.query("""

    select ans.nodeid, n2.geom from (
    
    --This query returns a table containing distinct from and to nodeids and geometries that are dead ends 
        
        select nodeid1 nodeid from (
        
        --This subquery returns a table containing distinct from and to nodeids that are dead ends
    
            
        select coalesce(nodeidto, nodeidfrom) nodeid1 from ( 
            
            --This subquery returns a table containing all distinct from and to nodeids that are not the from and to nodes
            --for multiple segments
            
            select distinct nodeidfrom from(
                select distinct l.nodeidfrom, l.street, l.geom from lion l
                join node n
                on n.nodeid = l.nodeidfrom::int 
                where rb_layer in ('R', 'B', 'N') 
                and featuretyp not in ('1', '2', '3', '7','8', 'A', 'F') --Removes all other elements that are not real streets
                and n.vintersect is null ) d -- Removes all virtual intersections
            group by nodeidfrom
            having count(nodeidfrom) = 1
            ) f

            full join

            (
            select nodeidto from(
                select distinct l.nodeidto, l.street, l.geom 
                from lion l
                join node n
                on n.nodeid = l.nodeidto::int 
                where rb_layer in ('R', 'B', 'N') 
                and featuretyp not in ('1', '2', '3', '7','8', 'A', 'F') --Removes all other elements that are not real streets
                and n.vintersect is null ) d  -- Removes all virtual intersections
            group by nodeidto
            having count(nodeidto) =1 
            ) t

        on f.nodeidfrom=t.nodeidto
        where f.nodeidfrom is null or t.nodeidto is null) tot

        join 

        (
        --This subquery returns a table containing all of the from and to nodeids that do not match. This query
        --joins on exclusion nodeidfrom and nodeidto
            
        select coalesce(nodeidto, nodeidfrom) nodeid2 from ( 
            select distinct l.nodeidfrom --table containing all distinct nodeidfrom nodes 
            from lion l
            join node n on
            n.nodeid = l.nodeidfrom::int
            where l.rb_layer in ('R', 'B', 'N') and l.featuretyp not in ('1', '2', '3', '7','8', 'A', 'F') --Removes all other elements that are not real streets
            and n.vintersect is null  -- Removes all virtual intersections
            ) f
            full join
            (
            select distinct l.nodeidto --table containing all distinct nodeidto nodes
            from lion l
            join node n on
            n.nodeid = l.nodeidto::int
            where l.rb_layer in ('R', 'B', 'N') and l.featuretyp not in ('1', '2', '3', '7','8', 'A', 'F') --Removes all other elements that are not real streets
            and n.vintersect is null  -- Removes all virtual intersections
            ) t 
        on f.nodeidfrom=t.nodeidto
        where f.nodeidfrom is null or t.nodeidto is null) tot2
        on tot.nodeid1=tot2.nodeid2) ans
    join node n2
    on ans.nodeid::int=n2.nodeid
    order by ans.nodeid
    """)

Dataframe containing all street end nodes and geometries

In [4]:
dead_ends=pd.DataFrame(dead_ends.data,columns=['nodeid','node_geom'])

Function for Writing dataframes to pgsql using sqlalchemy

In [5]:
from sqlalchemy import create_engine

def df_to_sql(df,tbl_name,db): 
    engine = create_engine('postgresql://{user}:{pw}@10.243.154.52:5432/CRASHDATA'.format(user=db.params['user'],
                                                                                          pw=db.params['password']),
                                                                                          echo=False)
    
    df.to_sql(name='{}'.format(tbl_name), con= engine, if_exists = 'replace', index=False)
    
    db.query("""ALTER TABLE {tbl}
                ALTER COLUMN geom TYPE Geometry USING geom::Geometry;
                
                grant all on {tbl} to public;""".format(tbl=tbl_name))
    
    return tbl_name

Writing dead_ends dataframe to table in pgsql

In [ ]:
dead_ends=df_to_sql(dead_ends,'dead_ends',cdb)
C:\Python27\ArcGIS10.4\lib\site-packages\sqlalchemy\dialects\postgresql\base.py:2972: SAWarning: Did not recognize type 'geometry' of column 'geom'
  "Did not recognize type '%s' of column '%s'" % (attype, name)

Dead ends shapefile creation

In [6]:
db2.pg_shp.export_pg_table_to_shp(r'{path}'.format(path=os.getcwd()),
                                  cdb,'dead_ends', shp_name='dead_ends' + str((datetime.datetime.now().strftime("_%Y-%m-%d_%H"))))
ogr2ogr -overwrite -f "ESRI Shapefile" "E:\RIS\Staff Folders\Samuel\Requests\AG\Dead_Ends\dead_ends_2019-09-05_16.shp" PG:"host=DOTDEVRHPGSQL01 user=soge dbname=CRASHDATA password=********" -sql "select * from public.dead_ends"
Done!

Map veiw of dead end nodes

In [13]:
from IPython.display import Image
PATH = "C:\Users\soge\Desktop"
Image(filename = PATH + "\dead_ends_map.png", width=1000, height=1000)
Out[13]:

Zoomed view of dead ends map

In [14]:
from IPython.display import Image
PATH = "C:\Users\soge\Desktop"
Image(filename = PATH + "\dead_ends_map_zoomed_2.png", width=1000, height=1000)
Out[14]:
In [ ]:
    select ans.nodeid, n2.geom from (

        select nodeid1 nodeid from (
        select coalesce(nodeidto, nodeidfrom) nodeid1 from ( 

            select distinct nodeidfrom from(
                select distinct l.nodeidfrom, l.street, l.geom from lion l
                join node n
                on n.nodeid = l.nodeidfrom::int 
                where rb_layer in ('R', 'B', 'N') 
                and featuretyp not in ('1', '2', '3', '7','8', 'A', 'F')
                and n.vintersect is null ) d
            group by nodeidfrom
            having count(nodeidfrom) = 1
            ) f

            full join

            (
            select nodeidto from(
                select distinct l.nodeidto, l.street, l.geom 
                from lion l
                join node n
                on n.nodeid = l.nodeidto::int 
                where rb_layer in ('R', 'B', 'N') 
                and featuretyp not in ('1', '2', '3', '7','8', 'A', 'F')
                and n.vintersect is null ) d
            group by nodeidto
            having count(nodeidto) =1 
            ) t

        on f.nodeidfrom=t.nodeidto
        where f.nodeidfrom is null or t.nodeidto is null) tot

        join 

        (
        select coalesce(nodeidto, nodeidfrom) nodeid2 from ( 
            select distinct l.nodeidfrom --nodeidfrom table
            from lion l
            join node n on
            n.nodeid = l.nodeidfrom::int
            where l.rb_layer in ('R', 'B', 'N') and l.featuretyp not in ('1', '2', '3', '7','8', 'A', 'F')
            and n.vintersect is null
            ) f
            full join
            (
            select distinct l.nodeidto --nodeidto table
            from lion l
            join node n on
            n.nodeid = l.nodeidto::int
            where l.rb_layer in ('R', 'B', 'N') and l.featuretyp not in ('1', '2', '3', '7','8', 'A', 'F')
            and n.vintersect is null
            ) t 
        on f.nodeidfrom=t.nodeidto
        where f.nodeidfrom is null or t.nodeidto is null) tot2
        on tot.nodeid1=tot2.nodeid2) ans
    join node n2
    on ans.nodeid::int=n2.nodeid
    order by ans.nodeid